package com.tom.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.tom.common.Constans;

public class JDBCUtil {
	
	/***
	 * 获取已经入库的URL地址
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static ArrayList<String> getUrls(String tableName) throws ClassNotFoundException, SQLException{
		Class.forName("com.mysql.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/qss";
		String user = "root";
		String pwd = "123456";
		Connection db = DriverManager.getConnection(url,user,pwd);
		Statement st = db.createStatement();
		String sql = "select url from "+tableName+"";
		ResultSet rs = st.executeQuery(sql);
		ArrayList<String> list = new ArrayList<String>();
		while(rs.next()){
			list.add(rs.getNString(1));
		}
		rs.close();
		st.close();
		db.close();
		return list;
	}
	/***
	 * 批量插入中融宝运营json
	 * @param list
	 * @param tableName
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static boolean insertYyJson(ArrayList<String> list,String tableName) throws ClassNotFoundException, SQLException{
		Class.forName("com.mysql.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/qss";
		String user = "root";
		String pwd = "123456";
		Connection db = DriverManager.getConnection(url,user,pwd);
		Statement st = db.createStatement();
		Date now = new Date(); 
    	SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    	String time = dateFormat.format(now); 
		String sql = "insert into "+tableName+" (leijitouzi,yuetouzi,7dayData,nianlingduan,yonghuzhucebili,dakehuzhanbi,borroweramount,todayhuoyueyonghu,biaodiqingkuang,rongziqixian,dishitouziTop10,zijinqianyi1L,touzi30day,yqCount,lilvzoushitouziuzi_16,amountyuqi,projectyuqi,yuqixiangqing,zuikuaimanbiao,zuimanmanbiao,rongzihuankuanjh,insert_time) values('"+list.get(0)+"','"+list.get(1)+"','"+list.get(2)+"','"+list.get(3)+"','"+list.get(4)+"','"+list.get(5)+"','"+list.get(6)+"','"+list.get(7)+"','"+list.get(8)+"','"+list.get(9)+"','"+list.get(10)+"','"+list.get(11)+"','"+list.get(12)+"','"+list.get(13)+"','"+list.get(14)+"','"+list.get(15)+"','"+list.get(16)+"','"+list.get(17)+"','"+list.get(18)+"','"+list.get(19)+"','"+list.get(20)+"','"+time+"')";
		st.addBatch(sql);
		st.executeBatch();
		st.close();
		db.close();
		return true;
	}
	
	/***
	 * 获取已经入库的中融宝JSON数据
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static ArrayList<HashMap<String, String>> getJsonData(String column,String tableName) throws ClassNotFoundException, SQLException{
		Class.forName("com.mysql.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/qss";
		String user = "root";
		String pwd = "123456";
		Connection db = DriverManager.getConnection(url,user,pwd);
		Statement st = db.createStatement();
		String sql = "select "+column+" from "+tableName+"";
		ResultSet rs = st.executeQuery(sql);
		ArrayList<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		while(rs.next()){
			HashMap<String, String> map = new HashMap<String, String>();
			map.put(column, rs.getNString(1));
			list.add(map);
		}
		rs.close();
		st.close();
		db.close();
		return list;
	}
	/***
	 * 中融宝json数据处理入库
	 * @param columns 插入表的字段数组
	 * @param param 所需要json数据处理的字段
	 * @param tableName 插入表的表名
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static void zrbInsertData(String [] columns,String param,String tableName){
		
		try {
			List<HashMap<String, String>> list = getJsonData(param,"qss_zhongrongbao_yysj_json");
			ArrayList<String> list2 = new ArrayList<String>();
			for (int i = 0; i < list.size(); i++) {
				HashMap<String, String> map = list.get(i);
//				System.err.println(map);
				JSONArray data = JSONArray.parseArray(map.get(param));
				for (int j = 0; j < data.size(); j++) {
					JSONObject jsonObject = JSONObject.parseObject(data.get(j).toString());
//					System.out.println(jsonObject);
					String value = "(";
					String column = "(";
					for(int k = 0; k < columns.length; k ++){
						column += ","+columns[k];
						value += "," + "'"+jsonObject.getString(columns[k])+"'";
						
					}
					Date now = new Date(); 
					SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
					String time = dateFormat.format(now);
					column = column.substring(0, 1)+column.substring(2)+",insertdate)";
					value = value.substring(0, 1)+value.substring(2)+","+"'"+time+"'"+")";
					String sql = "insert into "+tableName+""+column+" values "+value+"";
					System.out.println(""+i+":-----------------------------start----------------------------------------------------");
					System.err.println("表名:"+tableName);
					System.err.println("执行insert操作");
					System.err.println("sql:"+sql);
					System.out.println(""+i+":-----------------------------end------------------------------------------------------");
					list2.add(sql);
				}
			}
			insertData(list2);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/***
	 * 土豆金服json数据处理入库
	 * @param columns 插入表的字段数组
	 * @param param 所需要json数据处理的字段
	 * @param tableName 插入表的表名
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static void tdjfInsertData(String param,String [] columns,String insertTableName){
		try {
			List<HashMap<String, String>> list = JDBCUtil.getJsonData(param,"qss_tudoujinfu_json");
			ArrayList<String> sList = new ArrayList<String>();
			for(int i = 0; i < list.size(); i++){
				HashMap<String, String> hMap = list.get(i);
				String result = hMap.get(param);
				JSONObject jsonObject = JSONObject.parseObject(result);
				Map<String, Object> map =jsonObject;
				String sql = "";
				
			    for (Entry<String, Object> entry : map.entrySet()) {
			    	String column = "(";
					for(int k = 0; k < columns.length; k ++){
						column += ","+columns[k];
					}
					column = column.substring(0, 1)+column.substring(2)+",insertdate)";
			    	Date now = new Date(); 
					SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
					String time = dateFormat.format(now);
			    	sql = "insert into "+insertTableName+""+column+" values("+"'"+entry.getKey()+"'"+","+"'"+entry.getValue()+"'"+","+"'"+time+"'"+")";
			    	System.out.println(sql);
			    	sList.add(sql);
			    }     
			}
			JDBCUtil.insertData(sList);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/***
	 * 批量插入url
	 * @param list
	 * @param tableName
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static boolean insertUrls(ArrayList<String> list,String tableName) throws ClassNotFoundException, SQLException{
		Class.forName(Constans.JDBC);
		String url = Constans.JDBC_URL;
		String user = Constans.USER;
		String pwd = Constans.PWD;
		Connection db = DriverManager.getConnection(url,user,pwd);
		Statement st = db.createStatement();
		for(int i = 0 ; i < list.size() ; i ++){
			Date now = new Date(); 
    		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    		String time = dateFormat.format(now); 
			String sql = "insert into "+tableName+" (url,insert_time) values('"+list.get(i)+"','"+time+"')";
			st.addBatch(sql);
		}
		st.executeBatch();
		st.close();
		db.close();
		return true;
	}
	/***
	 * 批量插入data
	 * @param list
	 * @param tableName
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static boolean insertData(ArrayList<String> list) throws ClassNotFoundException, SQLException{
		Class.forName(Constans.JDBC);
		String url = Constans.JDBC_URL;;
		String user = Constans.USER;
		String pwd = Constans.PWD;
		Connection db = DriverManager.getConnection(url,user,pwd);
		Statement st = db.createStatement();
		for(int i = 0 ; i < list.size() ; i ++){
			st.addBatch(list.get(i));
		}
		st.executeBatch();
		st.close();
		db.close();
		return true;
	}
	/***
	 * 获取已经入库的URL地址
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static ArrayList<String> getUrls() throws ClassNotFoundException, SQLException{
		Class.forName(Constans.JDBC);
		String url = Constans.JDBC_URL;;
		String user = Constans.USER;
		String pwd = Constans.PWD;
		Connection db = DriverManager.getConnection(url,user,pwd);
		Statement st = db.createStatement();
		String sql = "select url from qss_zhongrongbao_url";
		ResultSet rs = st.executeQuery(sql);
		ArrayList<String> list = new ArrayList<String>();
		while(rs.next()){
			list.add(rs.getNString(1));
		}
		rs.close();
		st.close();
		db.close();
		return list;
	}
	/***
	 * 获取已经入库的JSON数据
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static ArrayList<HashMap<String, String>> getJsonData() throws ClassNotFoundException, SQLException{
		Class.forName(Constans.JDBC);
		String url = Constans.JDBC_URL;;
		String user = Constans.USER;
		String pwd = Constans.PWD;
		Connection db = DriverManager.getConnection(url,user,pwd);
		Statement st = db.createStatement();
		String sql = "select item_id,json1,json2,json3,json4,json5 from qss_zhongrongbao_itemdata ";
		ResultSet rs = st.executeQuery(sql);
		ArrayList<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		while(rs.next()){
			HashMap<String, String> map = new HashMap<String, String>();
			map.put("item_id", rs.getNString(1));
			map.put("json1", rs.getNString(2));
			map.put("json2", rs.getNString(3));
			map.put("json3", rs.getNString(4));
			map.put("json4", rs.getNString(5));
			map.put("json5", rs.getNString(6));
			list.add(map);
		}
		rs.close();
		st.close();
		db.close();
		return list;
	}
	/***
	 * 插入json数据
	 * @param list
	 * @param list1
	 * @param list2
	 * @param list3
	 * @param list4
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static boolean insertJson(ArrayList<String> list,ArrayList<String> list1,ArrayList<String> list2,ArrayList<String> list3,ArrayList<String> list4,ArrayList<String> list5) throws ClassNotFoundException, SQLException{
		Class.forName(Constans.JDBC);
		String url = Constans.JDBC_URL;;
		String user = Constans.USER;
		String pwd = Constans.PWD;
		Connection db = DriverManager.getConnection(url,user,pwd);
		Statement st = db.createStatement();
		for(int i = 0 ; i < list1.size() ; i ++){
			Date now = new Date(); 
    		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    		String time = dateFormat.format(now); 
			String sql = "INSERT into qss_zhongrongbao_itemdata(item_id,json1,json2,json3,json4,json5,insert_time) values('"+list.get(i)+"','"+list1.get(i)+"','"+list2.get(i)+"','"+list3.get(i)+"','"+list4.get(i)+"','"+list5.get(i)+"','"+time+"')";
			st.addBatch(sql);
		}
		st.executeBatch();
		st.close();
		db.close();
		return true;
	}
	/***
	 * 插入京东的json数据
	 * @param list
	 * @param tableName
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static boolean insertJDJson(ArrayList<String> list,String tableName) throws ClassNotFoundException, SQLException{
		Class.forName(Constans.JDBC);
		String url = "jdbc:mysql://localhost:3306/tom";
		String user = Constans.USER;
		String pwd = Constans.PWD;
		Connection db = DriverManager.getConnection(url,user,pwd);
		Statement st = db.createStatement();
		for(int i = 0 ; i < list.size() ; i ++){
			Date now = new Date(); 
    		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    		String time = dateFormat.format(now); 
			String sql = "insert into "+tableName+" (item_id,item_comment_json,insert_time) values('1909100','"+list.get(i)+"','"+time+"')";
			st.addBatch(sql);
		}
		st.executeBatch();
		st.close();
		db.close();
		return true;
	}
	/***
	 * 获取数据库中京东的json数据
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static ArrayList<String> getJDJson() throws ClassNotFoundException, SQLException{
		Class.forName(Constans.JDBC);
		String url = "jdbc:mysql://localhost:3306/tom";
		String user = Constans.USER;
		String pwd = Constans.PWD;
		Connection db = DriverManager.getConnection(url,user,pwd);
		Statement st = db.createStatement();
		String sql = "select item_comment_json from jd_comment";
		ResultSet rs = st.executeQuery(sql);
		ArrayList<String> list = new ArrayList<String>();
		while(rs.next()){
			list.add(rs.getNString(1));
		}
		rs.close();
		st.close();
		db.close();
		return list;
	}
	/***
	 * json数据处理
	 * @param list 评论
	 * @param score 得分
	 * @param tableName
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static boolean JDJsonTransfer(ArrayList<String> list,ArrayList<String> score,String tableName) throws ClassNotFoundException, SQLException{
		Class.forName(Constans.JDBC);
		String url = "jdbc:mysql://localhost:3306/tom";
		String user = Constans.USER;
		String pwd = Constans.PWD;
		Connection db = DriverManager.getConnection(url,user,pwd);
		Statement st = db.createStatement();
		for(int i = 0 ; i < list.size() ; i ++){
			Date now = new Date(); 
    		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    		String time = dateFormat.format(now); 
			String sql = "insert into "+tableName+" (item_id,comment,score,insert_time) values('1909100','"+list.get(i)+"','"+score.get(i)+"','"+time+"')";
			st.addBatch(sql);
		}
		st.executeBatch();
		st.close();
		db.close();
		return true;
	}
	
	/***
	 * 插入携程数据
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static boolean CtripJsonTransfer(ArrayList<String> list) throws ClassNotFoundException, SQLException{
		Class.forName(Constans.JDBC);
		String url = "jdbc:mysql://localhost:3306/tom";
		String user = Constans.USER;
		String pwd = Constans.PWD;
		Connection db = DriverManager.getConnection(url,user,pwd);
		Statement st = db.createStatement();
		for(int i = 0 ; i < list.size() ; i ++){
			st.addBatch(list.get(i));
		}
		st.executeBatch();
		st.close();
		db.close();
		return true;
	}
}
